package org.me.runaway;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;


public class DB_Adapter{

    private Context context = null;

    private static final String TAG = "DBAdapter";
    private static final String DATABASE_NAME = "PlacesList";
    private static final String DATABASE_TABLE = "places";
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_CREATE = "create table "
            + DATABASE_TABLE + " (" + Place.KEY_ROWID
            + " integer primary key autoincrement, " + Place.KEY_NAME
            + " text not null," + Place.KEY_LAT + " text not null,"
            + Place.KEY_LNG + " text not null," + Place.KEY_ADDRESS
            + " text not null," + Place.KEY_DETAILS + " text not null,"
            + Place.KEY_PRICE + " text not null," + Place.KEY_SCHEDULE
            + " text not null," + Place.KEY_CONTACT1 + " text not null,"
            + Place.KEY_CONTACT2 + " text not null," + Place.KEY_FAVORITE
            + " text not null," + Place.KEY_RATE + " text not null,"
            + Place.KEY_TYPE + " text not null," + Place.KEY_IMAGE
            + " text not null," + Place.KEY_UPDATED + " text not null,"
            + Place.KEY_VOTED + " text not null);";
  
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DB_Adapter(Context ctx) {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS titles");
            onCreate(db);
        }
    }

    public DB_Adapter open() throws SQLException {
        db = DBHelper.getWritableDatabase();

        return this;
    }

    public void close() {
        DBHelper.close();
    }
    public static final String[] PLACE_SCHEMA = new String[]{Place.KEY_ROWID,
        Place.KEY_NAME, Place.KEY_LAT, Place.KEY_LNG, Place.KEY_ADDRESS,
        Place.KEY_DETAILS, Place.KEY_PRICE, Place.KEY_SCHEDULE, Place.KEY_CONTACT1,
        Place.KEY_CONTACT2, Place.KEY_FAVORITE, Place.KEY_RATE, Place.KEY_TYPE,
        Place.KEY_IMAGE, Place.KEY_UPDATED, Place.KEY_VOTED};

    public Cursor getAllPlaces() {
        return db.query(DATABASE_TABLE, PLACE_SCHEMA, null, null, null, null, null);
    }

    public Cursor getPlaceID(long rowId) throws SQLException {
        Cursor mCursor = db.query(true, DATABASE_TABLE, PLACE_SCHEMA,
                Place.KEY_ROWID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    public long getId(String name) {
        String[] columns = {Place.KEY_ROWID};
        Cursor cs = db.query(DATABASE_TABLE, columns, Place.KEY_NAME + "='" + name + "'", null, null, null, null);

        if (cs != null) {
            cs.moveToFirst();
            return cs.getLong(cs.getColumnIndex(Place.KEY_ROWID));
        }

        return -1;
    }

    public Place getPlace(long rowId) {
        Cursor mCursor = db.query(true, DATABASE_TABLE, PLACE_SCHEMA,
                Place.KEY_ROWID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();

            return getPlaceFromCursor(mCursor);
        }
        return null;
    }

    public long insertPlace(Place p) {
        if (p != null) {
            return insertPlace(p.getName(), String.valueOf(p.getLatitude()), String.valueOf(p.getLongitude()),
                    p.getAddress(), p.getDetails(), String.valueOf(p.getPrice()), p.getSchedule(),
                    String.valueOf(p.getContact1()), String.valueOf(p.getContact2()),
                    String.valueOf(p.isFavorite()), p.getRate(), p.getType(), p.getImage(),
                    String.valueOf(p.isUpdated()), String.valueOf(p.isVoted()));
        }

        return -1;
    }

    public long insertPlace(String name, String latitude, String longitude,
            String address, String details, String price, String schedule, String contact1,
            String contact2, String favorite, String rate, String type, String image, String updated, String voted) {

        ContentValues initialValues = new ContentValues();
        initialValues.put(Place.KEY_NAME, name);
        initialValues.put(Place.KEY_LAT, latitude);
        initialValues.put(Place.KEY_LNG, longitude);
        initialValues.put(Place.KEY_ADDRESS, address);
        initialValues.put(Place.KEY_DETAILS, details);
        initialValues.put(Place.KEY_PRICE, price);
        initialValues.put(Place.KEY_SCHEDULE, schedule);
        initialValues.put(Place.KEY_CONTACT1, contact1);
        initialValues.put(Place.KEY_CONTACT2, contact2);
        initialValues.put(Place.KEY_FAVORITE, favorite);
        initialValues.put(Place.KEY_RATE, rate);
        initialValues.put(Place.KEY_TYPE, type);
        initialValues.put(Place.KEY_IMAGE, image);
        initialValues.put(Place.KEY_UPDATED, updated);
        initialValues.put(Place.KEY_VOTED, voted);

        return db.insert(DATABASE_TABLE, null, initialValues);
    }

    public Place getPlaceFromCursor(Cursor c) {

        Place p = new Place(c.getString(Place.NAME_ID), c.getDouble(Place.LAT_ID),
                c.getDouble(Place.LNG_ID), c.getString(Place.ADDRESS_ID),
                c.getString(Place.DETAILS_ID), c.getInt(Place.PRICE_ID), c.getString(Place.SCHEDULE_ID),
                c.getInt(Place.CONTACT1_ID), c.getInt(Place.CONTACT2_ID), Boolean.valueOf(c.getString(Place.FAVORITE_ID)),
                c.getString(Place.RATE_ID), c.getString(Place.TYPE_ID), c.getString(Place.IMAGE_ID),
                Boolean.valueOf(c.getString(Place.UPDATED_ID)), Boolean.valueOf(c.getString(Place.VOTED_ID)));
        p.setId(c.getInt(Place.ID));
        return p;
    }

    public boolean deletePlaceId(long rowId) {
        return deletePlace(rowId);
    }

    public boolean deletePlace(Place p) {
        if (p != null) {
            return deletePlace(p.getId());
        }
        return false;
    }

    public boolean deletePlace(long id) {
        return db.delete(DATABASE_TABLE, Place.KEY_ROWID + "=" + id, null) > 0;
    }

    public boolean updatePlace(long rowId, Place p) {
        if (p != null) {
            return updatePlace(rowId, p.getName(), String.valueOf(p.getLatitude()), String.valueOf(p.getLongitude()),
                    p.getAddress(), p.getDetails(), String.valueOf(p.getPrice()), p.getSchedule(),
                    String.valueOf(p.getContact1()), String.valueOf(p.getContact2()),
                    String.valueOf(p.isFavorite()), p.getRate(), p.getType(), p.getImage(),
                    String.valueOf(p.isUpdated()), String.valueOf(p.isVoted()));
        }
        return false;
    }

    public boolean updatePlace(long rowId, String name, String latitude, String longitude,
            String address, String details, String price, String schedule, String contact1,
            String contact2, String favorite, String rate, String type, String image, String updated,
            String voted) {
        ContentValues args = new ContentValues();
        args.put(Place.KEY_NAME, name);
        args.put(Place.KEY_LAT, latitude);
        args.put(Place.KEY_LNG, longitude);
        args.put(Place.KEY_ADDRESS, address);
        args.put(Place.KEY_DETAILS, details);
        args.put(Place.KEY_PRICE, price);
        args.put(Place.KEY_SCHEDULE, schedule);
        args.put(Place.KEY_CONTACT1, contact1);
        args.put(Place.KEY_CONTACT2, contact2);
        args.put(Place.KEY_FAVORITE, favorite);
        args.put(Place.KEY_RATE, rate);
        args.put(Place.KEY_TYPE, type);
        args.put(Place.KEY_IMAGE, image);
        args.put(Place.KEY_UPDATED, updated);
        args.put(Place.KEY_VOTED, voted);

        return db.update(DATABASE_TABLE, args, Place.KEY_ROWID + "=" + rowId,
                null) > 0;
    }

    public Cursor query(String rowId, String selection, String[] selectionArgs,
            String sortOrder) {

        SQLiteQueryBuilder sqlBuilder = new SQLiteQueryBuilder();
        sqlBuilder.setTables(DATABASE_TABLE);

        if (rowId != null) {
            sqlBuilder.appendWhere(Place.KEY_ROWID + " = " + rowId);
        }

        if (sortOrder == null || sortOrder == "") {
            sortOrder = Place.KEY_NAME;
        }

        Cursor c = sqlBuilder.query(db, PLACE_SCHEMA, selection, selectionArgs,
                null, null, sortOrder);

        return c;
    }

    public long insertPlace(ContentValues values) {
        return db.insert(DATABASE_TABLE, "", values);
    }

    public long deletePlaces(String whereClause, String[] whereArgs) {
        return db.delete(DATABASE_TABLE, whereClause, whereArgs);
    }

    public int update(ContentValues values, String selection,
            String[] selectionArgs) {
        int count = 0;

        count = db.update(DATABASE_TABLE, values, selection, selectionArgs);

        return count;
    }

    public boolean queryByName(String name) {
        String[] columns = {Place.KEY_NAME};
        Cursor cs = db.query(DATABASE_TABLE, columns, columns[0] + "='" + name + "'", null, null, null, null);

        if (cs.getCount() > 0) {
            cs.moveToFirst();
            int i = cs.getColumnIndex(Place.KEY_NAME);
            String rowName = cs.getString(i);
            if (rowName.equals(name)) {
                return true;
            } else {
                return false;
            }
        }

        return false;
    }

    public Cursor queryFavorites() {
        return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_FAVORITE + "='true'", null, null, null, null);
    }

    public Cursor querySearch(String name) {
        return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_NAME + " like '%" + name + "%'", null, null, null, null);
    }

    public Cursor querySearchHotel(String name) {
        if (name.length() > 0) {
            return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_NAME + " like '%" + name + "%' AND " + Place.KEY_TYPE + "='Hotel'", null, null, null, null);
        } else {
            return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_TYPE + "='Hotel'", null, null, null, null);
        }
    }

    public Cursor queryPositions() {
        return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_ROWID, null, null, null, null);

    }

    public Cursor querySearchRestaurant(String name) {
        if (name.length() > 0) {
            return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_NAME + " like '%" + name + "%' AND " + Place.KEY_TYPE + "='Restaurante'", null, null, null, null);
        } else {
            return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_TYPE + "='Restaurante'", null, null, null, null);
        }
    }

    public Cursor querySearchMuseum(String name) {
        if (name.length() > 0) {
            return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_NAME + " like '%" + name + "%' AND " + Place.KEY_TYPE + "='Museu'", null, null, null, null);
        } else {
            return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_TYPE + "='Museu'", null, null, null, null);
        }
    }

    public Cursor querySearchTwoTypes(String name, int op) {
        switch (op) {
            case 0:
                if (name.length() > 0)
                    return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_NAME + " like '%" + name + "%' AND (" + Place.KEY_TYPE + "='Hotel' OR " + Place.KEY_TYPE + "='Restaurante')", null, null, null, null);
                else
                    return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_TYPE + "='Hotel' OR " + Place.KEY_TYPE + "='Restaurante'", null, null, null, null);
            case 1:
                return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_NAME + " like '%" + name + "%' AND (" + Place.KEY_TYPE + "='Hotel' OR " + Place.KEY_TYPE + "='Museu')", null, null, null, null);
            case 2:
                return db.query(DATABASE_TABLE, PLACE_SCHEMA, Place.KEY_NAME + " like '%" + name + "%' AND (" + Place.KEY_TYPE + "='Museu' OR " + Place.KEY_TYPE + "='Restaurante')", null, null, null, null);
        }

        return null;
    }
}
